Imports System.Data.OleDb

Public Class ProjectTrackingUpgrader


    Private Const CreateCustomerTableSQL As String = "CREATE TABLE Customer (customer_id COUNTER(1,1) NOT NULL PRIMARY KEY, customer_name TEXT(255) NOT NULL UNIQUE, customer_address MEMO, customer_phone TEXT(255))"
    Private Const CreateProjectTypeTableSQL As String = "CREATE TABLE ProjectType (type_id COUNTER(1,1) NOT NULL PRIMARY KEY, type_name TEXT(255) NOT NULL UNIQUE)"
    Private Const CreateProjectStatusTableSQL As String = "CREATE TABLE ProjectStatus (status_id COUNTER(1,1) NOT NULL PRIMARY KEY, status TEXT(255))"

    Private Const CreateProjectTableSQL As String = "CREATE TABLE Project (project_id COUNTER(1,1) NOT NULL PRIMARY KEY, project_name TEXT(255) NOT NULL, description MEMO, start_date DATE NOT NULL, deadline DATE NOT NULL, customer_id INTEGER NOT NULL REFERENCES Customer(customer_id), status_id INTEGER NOT NULL REFERENCES ProjectStatus(status_id))"

    Private Const CreateProjectMaterialsTableSQL As String = "CREATE TABLE ProjectMaterials (materials_id COUNTER(1,1) NOT NULL PRIMARY KEY, material_name TEXT(255), material_cost SINGLE NOT NULL, project_id INTEGER NOT NULL REFERENCES Project(project_id))"
    Private Const CreateProjectProductsUsedTableSQL As String = "CREATE TABLE ProjectProducts (project_projects_id COUNTER(1,1) NOT NULL PRIMARY KEY, project_id INTEGER NOT NULL REFERENCES Project(project_id), file_id INTEGER NOT NULL REFERENCES Files(file_id))"

    Private Const ExpectedTableExistsErrorCode As Integer = -2147217900

    Private Sub New()

    End Sub


    Public Shared Function CreateProjectTables() As Boolean

        Dim success As Boolean = False

        Dim conn As New OleDbConnection
        conn.ConnectionString = My.Settings.PoserFilesConnectionString

        Try

            conn.Open()

            Dim cm As New OleDbCommand
            cm.Connection = conn
            cm.CommandType = CommandType.Text
            cm.CommandText = CreateProjectTypeTableSQL
            Try
                cm.ExecuteNonQuery()
            Catch ex As OleDbException
                If ex.ErrorCode <> ExpectedTableExistsErrorCode Then
                    Throw
                End If
            End Try

            cm = New OleDbCommand
            cm.Connection = conn
            cm.CommandType = CommandType.Text
            cm.CommandText = CreateCustomerTableSQL
            Try
                cm.ExecuteNonQuery()
            Catch ex As OleDbException
                If ex.ErrorCode <> ExpectedTableExistsErrorCode Then
                    Throw
                End If
            End Try

            cm = New OleDbCommand
            cm.Connection = conn
            cm.CommandType = CommandType.Text
            cm.CommandText = CreateProjectStatusTableSQL
            Try
                cm.ExecuteNonQuery()
            Catch ex As OleDbException
                If ex.ErrorCode <> ExpectedTableExistsErrorCode Then
                    Throw
                End If
            End Try

            cm = New OleDbCommand
            cm.Connection = conn
            cm.CommandType = CommandType.Text
            cm.CommandText = CreateProjectTableSQL
            Try
                cm.ExecuteNonQuery()
            Catch ex As OleDbException
                If ex.ErrorCode <> ExpectedTableExistsErrorCode Then
                    Throw
                End If
            End Try

            cm = New OleDbCommand
            cm.Connection = conn
            cm.CommandType = CommandType.Text
            cm.CommandText = CreateProjectMaterialsTableSQL
            Try
                cm.ExecuteNonQuery()
            Catch ex As OleDbException
                If ex.ErrorCode <> ExpectedTableExistsErrorCode Then
                    Throw
                End If
            End Try

            cm = New OleDbCommand
            cm.Connection = conn
            cm.CommandType = CommandType.Text
            cm.CommandText = CreateProjectProductsUsedTableSQL
            Try
                cm.ExecuteNonQuery()
            Catch ex As OleDbException
                If ex.ErrorCode <> ExpectedTableExistsErrorCode Then
                    Throw
                End If
            End Try

            success = True

        Catch ex As OleDbException
            UpgradeLog.WriteEntry("CreateProjectTables failed: " & ex.Message)
        Catch ex As Exception
            UpgradeLog.WriteEntry("CreateProjectTables failed: " & ex.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then
                conn.Close()
            End If
        End Try

        Return success

    End Function

End Class
